import tormysql
from tornado import gen
from tormysql import DictCursor

"""
    AsyncTorMysql:
        get_pool：获取请求池
        do_close：关闭连接
        
        set_cursor_dict：设置True返回数据为字典对象
        get_cursor_dict：获取当前的cursor类型对象
        
        其它方法：参照syncDatabase
"""


class AsyncTorMysql:

    def __init__(self, max_connections, idle_seconds, wait_connection_timeout, host, user, password, db,
                 charset="utf8"):
        self.mysql_pool = tormysql.ConnectionPool(max_connections=max_connections, idle_seconds=idle_seconds,
                                                  wait_connection_timeout=wait_connection_timeout, host=host, user=user,
                                                  passwd=password, db=db, charset=charset)
        self.__c_dict = None

    def set_cursor_dict(self, cd=False):
        self.__c_dict = DictCursor if cd else None

    def get_cursor_dict(self):
        return self.__c_dict

    @gen.coroutine
    def select(self, sql, content_list=None):
        with (yield self.mysql_pool.Connection()) as tor_conn:
            with tor_conn.cursor(self.__c_dict) as tor_cursor:
                if content_list:
                    yield tor_cursor.execute(sql, content_list)
                else:
                    yield tor_cursor.execute(sql)
                data = tor_cursor.fetchall()
        return data

    @gen.coroutine
    def select_wsw(self, which, what="*"):
        sql = "select {} from {}".format(what, which)
        data = yield self.select(sql)
        return data

    @gen.coroutine
    def select_wsw_w(self, which, what, where):
        sql = "select {} from {}".format(what, which)
        where_sql = "where "
        content_list = []
        tmp_count = 1
        for i in where:
            if tmp_count > 1:
                where_sql += "and "
            content_list.append(where.get(i))
            where_sql += i
            where_sql += "%s "
            tmp_count += 1
        data = yield self.select(sql + " " + where_sql, content_list)
        return data

    @gen.coroutine
    def update(self, sql, content_list=None):
        with (yield self.mysql_pool.Connection()) as tor_conn:
            with tor_conn.cursor(self.__c_dict) as tor_cursor:
                try:
                    if content_list:
                        affect = yield tor_cursor.execute(sql, content_list)
                    else:
                        affect = yield tor_cursor.execute(sql)
                except Exception as e:
                    print(e)
                    yield tor_conn.rollback()
                    return 0, 0
                else:
                    yield tor_conn.commit()
                    return affect, tor_cursor.lastrowid

    @gen.coroutine
    def insert_data_many(self, sql, content_list):
        with (yield self.mysql_pool.Connection()) as tor_conn:
            with tor_conn.cursor(self.__c_dict) as tor_cursor:
                try:
                    affect = yield tor_cursor.executemany(sql, content_list)
                    yield tor_conn.commit()
                    return affect
                except Exception as e:
                    print(e)
                    yield tor_conn.rollback()
                    return 0

    @gen.coroutine
    def insert_data(self, which, what, field=(), many=False):
        if len(field) == 0:
            field = ""
        sql = "insert into "
        sql += which
        if len(field) != 0:
            sql += "(" + ",".join(field) + ")"
        sql += " values"
        if many:
            sql += str(["%s"] * len(what)).replace("'", "").replace("[", "(").replace("]", ")")
            data = yield self.insert_data_many(sql, what)
            return data
        sql += str(["%s"] * len(what)).replace("'", "").replace("[", "(").replace("]", ")")
        data = yield self.update(sql, what)
        return data

    @gen.coroutine
    def change_data(self, which, change_dict, where=None):
        content_list = []
        sql = "update "
        sql += which
        sql += " set "
        change_list = []
        for i in change_dict:
            change_list.append(i + "=" + "%s")
        sql += ",".join(change_list)
        content_list.extend(change_dict.values())
        if where:
            where_list = []
            for k in where:
                where_list.append(k + "%s")
            sql += " where " + " and ".join(where_list)
            content_list.extend(tuple(where.values()))
        data = yield self.update(sql, content_list)
        return data

    @gen.coroutine
    def delete_data(self, table, where=None):
        sql = "delete from {}".format(table)
        if not where:
            return
        where_list = []
        for k in where:
            where_list.append(k + "%s")
        sql += " where " + " and ".join(where_list)
        data = yield self.update(sql, tuple(where.values()))
        return data

    @gen.coroutine
    def fast_clear_table(self, table):
        yield self.update("set foreign_key_checks=0")
        yield self.update("truncate table {}".format(table))
        yield self.update("set foreign_key_checks=1")

    @gen.coroutine
    def clear_table(self, table):
        data = yield self.update("delete from {}".format(table))
        return data

    def get_pool(self):
        return self.mysql_pool

    def do_close(self):
        self.mysql_pool.close()
